const express = require('express')

const router = express.Router()

const {db,genid} = require('../db/DbUtils')

//增加博客
router.post('/_token/add',async(req,res)=>{
    let {title,categoryId,content} = req.body
    let id = genid.NextId()
    let create_time = new Date().getTime()
    
    const inerst_sql = 'insert into `blog`(`id`,`title`,`category_id`,`content`,`create_time`)values(?,?,?,?,?)'
    let params = [id,title,categoryId,content,create_time]

    let{err,rows} = await db.async.run(inerst_sql,params)

    if(err == null){
        res.send({
            code:200,
            msg:'博客表添加成功',

        })
    }else{
        res.send({
            code:500,//可以协商自定义
            msg:'博客表添加失败',
            
        })
    }
})
//修改博客
router.put('/_token/update',async(req,res)=>{

    let {id,title,categoryId,content} = req.body
    
    const update_sql = 'update `blog` set `title` = ? , `content` = ? ,`category_id` = ? where `id` = ? '
    let params = [title,content,categoryId,id]

    let{err,rows} = await db.async.run(update_sql,params)

    if(err == null){
        res.send({
            code:200,
            msg:'博客表修改成功',

        })
    }else{
        res.send({
            code:500,//可以协商自定义
            msg:'博客表修改失败',
            
        })
    }
})

//删除博客
router.delete('/_token/delete',async (req,res)=>{

    let id= req.query.id
    const delete_sql ='delete from `blog` where `id` = ?'
    let {err,rows}=await db.async.run(delete_sql,[id])


    if(err == null){
        res.send({
            code:200,
            msg:'博客表删除成功',

        })
    }else{
        res.send({
            code:500,//可以协商自定义
            msg:'博客表删除失败',
            
        })
    }
})
//查询博客
router.get('/search',async (req,res)=>{
    /**
     * keyword 关键字 -标题内的关键字/文字内的关键字
     * categoryId 分类id 文章id
     * 同时存在关键字和分类id 则需要并列查询sql拼装
     * 无条件查询
     * 
     * 
     * 分页
     * page 页码
     * pageSIze 分页大小
     */
    let {keyword,categoryId,page,pageSize} = req.query

    //无数据的判断
    page = page == null ? 1 : page;
    pageSize = pageSize == null ? 10 : pageSize;
    categoryId = categoryId == null ? 0 : categoryId;
    keyword = keyword == null ? "" : keyword;


    //并列查询--sql拼装
    let params = []
    let whereSqls = []
    
    if(categoryId != 0 ){
        whereSqls.push(" `category_id` = ? ")
        params.push(categoryId)
    }

    if(keyword != "" ){
        whereSqls.push("( `title` LIKE ? OR `content` LIKE ?) ")
        params.push("%" + keyword + "%")
        params.push("%" + keyword + "%")
    }

    let whereSqlStr = ""
    if(whereSqls.length > 0){
        whereSqlStr = " where " + whereSqls.join(' and ')
    }

    //查询分页数据
    let SearchSql = " SELECT `id`,`category_id`,`create_time`,`title`,substr(`content`,0,50) AS `content` FROM `blog` " + whereSqlStr + " ORDER BY `create_time` DESC LIMIT ?,? "
    //分页算法
    let SearchSqlParams = params.concat([(page-1)*pageSize,pageSize])
    
    //查询数据总数
    let searchCountSql = 'select count(*) as `count`  from `blog` '+ whereSqlStr
    let SearchCountParams = params

    let searchResult = await db.async.all(SearchSql,SearchSqlParams)
    let countResult = await db.async.all(searchCountSql,SearchCountParams)

    console.log(searchResult,countResult)    

    if((searchResult.err == null)&&(countResult.err == null)){
        res.send({
            code:200,
            msg:'博客表查询成功',
            data:{
                keyword,
                categoryId,
                page,
                pageSize,
                rows:searchResult.rows,
                count:countResult.rows[0].count
            }
        })
    }else{
        res.send({
            code:500,
            msg:'博客表查询失败',
        })
    }
})

//需要暴露router
module.exports = router

